﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace Common.Unit
{
    /// <summary>
    /// Excel生成操作类
    /// Author: lyb
    /// Create: 2011-11-11
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// 导出列名
        /// </summary>
        public static System.Collections.SortedList ListColumnsName;
        public static bool IsStats = false;
        public static string StatsColName = string.Empty;

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public static void ExportExcel(DataTable dtSource, string filePath)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));
            HSSFWorkbook workbook = CreateExcelFile();
            InsertRow(dtSource, workbook, null);
            SaveExcelFile(workbook, filePath);
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="filePath"></param>
        public static void ExportExcel(DataTable dtSource, Stream excelStream)
        {
            if (ListColumnsName == null || ListColumnsName.Count == 0)
                throw (new Exception("请对ListColumnsName设置要导出的列明！"));

            HSSFWorkbook CurrentWorkbook = CreateExcelFile();
            // InsertRow(dtSource, CurrentWorkbook);
            SaveExcelFile(CurrentWorkbook, excelStream);
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        public static void SaveExcelFile(HSSFWorkbook workbook, string filePath)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                workbook.Dispose();
                var dir = Path.GetDirectoryName(filePath);
                if (!System.IO.Directory.Exists(dir))
                    System.IO.Directory.CreateDirectory(dir);
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <param name="filePath"></param>
        public static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
        {
            try
            {
                excelWorkBook.Write(excelStream);
            }
            finally
            {

            }
        }
        /// <summary>
        /// 创建Excel文件
        /// </summary>
        /// <param name="filePath"></param>
        public static HSSFWorkbook CreateExcelFile()
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            return hssfworkbook;
        }

        /// <summary>
        /// 创建Sheet
        /// </summary>
        /// <param name="filePath"></param>
        //public static HSSFSheet CreateSheet(String sheetName)
        //{
        //    ISheet newsheet = null;
        //    newsheet = CurrentWorkbook.CreateSheet(sheetName);
        //    return newsheet;
        //}

        //public static void CreateHeader(HSSFSheet sheet, Int32 rowIndex, String title)
        //{
        //    HSSFRow row = sheet.CreateRow(0);
        //    HSSFCell cell = row.CreateCell(0);
        //    cell.SetCellValue(title);
        //    HSSFCellStyle style = CurrentWorkbook.CreateCellStyle();
        //    style.Alignment = CellHorizontalAlignment.CENTER;
        //    style.FillBackgroundColor = HSSFColor.BLUE.index;

        //    HSSFFont font = CurrentWorkbook.CreateFont();
        //    font.FontHeight = 20 * 20;
        //    style.SetFont(font);
        //    cell.CellStyle = style;
        //    sheet.AddMergedRegion(new Region(0, 0, 0, 5));
        //}

        /// <summary>
        /// 创建excel表头
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="excelSheet"></param>
        public static void CreateHeader(HSSFWorkbook workbook, ISheet excelSheet)
        {
            int cellIndex = 0;
            IRow newRow = excelSheet.CreateRow(0);
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            style.FillPattern = FillPatternType.LEAST_DOTS;
            style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.Boldweight = 700;
            style.SetFont(font);
            //循环导出列
            foreach (System.Collections.DictionaryEntry de in ListColumnsName)
            {
                ICell newCell = newRow.CreateCell(cellIndex);
                newCell.CellStyle = style;
                newCell.SetCellValue(de.Value.ToString());
                cellIndex++;
            }
        }
        /// <summary>
        /// 插入数据行
        /// </summary>
        protected static void InsertRow(DataTable dtSource, HSSFWorkbook workbook, String sheetName)
        {
            int rowCount = 0;
            int sheetCount = 1;
            double total = 0d;
            ISheet newsheet = null;

            //循环数据源导出数据集
            sheetName = sheetName ?? "Sheet" + sheetCount;
            newsheet = workbook.CreateSheet(sheetName);
            CreateHeader(workbook, newsheet);
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount++;
                //超出10000条数据 创建新的工作簿
                if (rowCount == 10000)
                {
                    rowCount = 1;
                    sheetCount++;
                    newsheet = workbook.CreateSheet("Sheet" + sheetCount);
                    CreateHeader(workbook, newsheet);
                }
                if (IsStats && !string.IsNullOrEmpty(StatsColName))
                {
                    double t = 0d;
                    double.TryParse(dr[StatsColName].ToString(), out t);
                    total += t;
                }
                IRow newRow = newsheet.CreateRow(rowCount);
                InsertCell(dtSource, dr, newRow, newsheet, workbook);
            }
            if (IsStats)
                InsertStats(rowCount, newsheet, workbook, total);
        }

        private static void InsertStats(int rowCount, ISheet newsheet, HSSFWorkbook workbook, double total)
        {
            rowCount++;
            IRow newRow = newsheet.CreateRow(rowCount);
            newRow.Height = 400;
            ICell newCellTitle = newRow.CreateCell(0, CellType.STRING);

            ICell newCellValue = newRow.CreateCell(2, CellType.NUMERIC);
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            //背景
            //style.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            //style.FillPattern = FillPatternType.LEAST_DOTS;
            //style.FillBackgroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 20;
            font.Boldweight = 100;
            style.SetFont(font);
            newsheet.AddMergedRegion(new CellRangeAddress(rowCount, rowCount, 0, 1));
            newsheet.AddMergedRegion(new CellRangeAddress(rowCount, rowCount, 2, ListColumnsName.Count - 1));
            newCellTitle.CellStyle = style;
            newCellValue.CellStyle = style;
            newCellTitle.SetCellValue("合计（元）");
            newCellValue.SetCellValue(total);
        }
        /// <summary>
        /// 导出数据行
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected static void InsertCell(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, HSSFWorkbook excelWorkBook)
        {
            //取得列宽
            Int32[] arrColWidth = new Int32[dtSource.Columns.Count];

            for (Int32 i = 0; i < dtSource.Rows.Count; i++)
            {
                for (Int32 j = 0; j < dtSource.Columns.Count; j++)
                {
                    Int32 intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    //标题宽度
                    string columnName = ListColumnsName.GetValueList()[j].ToString();
                    int len = Encoding.GetEncoding(936).GetBytes(columnName).Length;
                    if (intTemp < len)
                        intTemp = len;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
            {
                excelSheet.SetColumnWidth(cellIndex, (arrColWidth[cellIndex] + 5) * 256);
                //列名称
                string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
                ICell newCell = null;
                System.Type rowType = drSource[columnsName].GetType();
                string drValue = drSource[columnsName].ToString().Trim();
                switch (rowType.ToString())
                {
                    case "System.String"://字符串类型
                        drValue = drValue.Replace("&", "&");
                        drValue = drValue.Replace(">", ">");
                        drValue = drValue.Replace("<", "<");
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(dateV);

                        //格式化显示
                        ICellStyle cellStyle = excelWorkBook.CreateCellStyle();
                        IDataFormat format = excelWorkBook.CreateDataFormat();
                        cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
                        newCell.CellStyle = cellStyle;

                        break;
                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(intV.ToString());
                        break;
                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理
                        newCell = currentExcelRow.CreateCell(cellIndex);
                        newCell.SetCellValue("");
                        break;
                    default:
                        throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
                }
            }
        }



        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        /// <Author>lyb</Author>
        public static void Export(DataSet ds, String strFileName, String accessDate, Int32 memberHits, Int32 nonMemberHits, Int32 totalHits, String websiteName)
        {
            using (MemoryStream ms = Export(ds, accessDate, memberHits, nonMemberHits, totalHits, websiteName))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
        public static MemoryStream Export(DataSet ds, String accessDate, Int32 memberHits, Int32 nonMemberHits, Int32 totalHits, String websiteName)
        {
            Int32 rowIndex = 0;
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("数据分析表");

            DataTable dt1 = ds.Tables[0];
            DataTable dt2 = ds.Tables[1];
            DataTable dt3 = ds.Tables[2];
            Int32 dt1Cols = dt1.Columns.Count, dt2Cols = dt2.Columns.Count;
            Int32 dt1Rows = dt1.Rows.Count, dt2Rows = dt2.Rows.Count;
            String title1 = String.Format("{0}会员访问统计[{1}]分析报告", websiteName, accessDate),
                   title2 = String.Format("[{0}]地区统计", accessDate),
                   title3 = String.Format("[{0}]公司性质统计", accessDate);

            IRow headRow = sheet.CreateRow(rowIndex);
            headRow.HeightInPoints = 25;
            ICell cell = headRow.CreateCell(0);
            cell.SetCellValue(title1);
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            //背景
            style.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            style.FillPattern = FillPatternType.LEAST_DOTS;
            style.FillBackgroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.FontHeightInPoints = 12;
            style.SetFont(font);
            cell.CellStyle = style;
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt1Cols + dt2Cols - 1);
            sheet.AddMergedRegion(region);
            //((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, CellBorderType.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);

            rowIndex++;

            IRow headRow2 = sheet.CreateRow(1);
            headRow2.HeightInPoints = 25;
            ICell cell2 = headRow2.CreateCell(0);
            cell2.SetCellValue(title2);
            ICellStyle style2 = workbook.CreateCellStyle();
            style2.Alignment = HorizontalAlignment.CENTER;
            style2.VerticalAlignment = VerticalAlignment.CENTER;
            style2.FillForegroundColor = HSSFColor.DARK_YELLOW.index;
            style2.FillPattern = FillPatternType.LEAST_DOTS;
            style2.FillBackgroundColor = HSSFColor.DARK_YELLOW.index;
            style2.BorderLeft = CellBorderType.THIN;
            style2.LeftBorderColor = HSSFColor.BLACK.index;

            IFont font2 = workbook.CreateFont();
            font2.FontName = "宋体";
            font2.Boldweight = 700;
            style2.SetFont(font2);
            cell2.CellStyle = style2;
            CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, dt1Cols - 1);
            sheet.AddMergedRegion(region2);
            //((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region2, CellBorderType.THIN, NPOI.HSSF.Util.HSSFColor.YELLOW.index);

            ICell cell3 = headRow2.CreateCell(dt1Cols);
            cell3.SetCellValue(title3);
            cell3.CellStyle = style2;
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, dt1Cols, dt1Cols + dt2Cols - 1));
            CellRangeAddress region3 = new CellRangeAddress(1, 1, dt1Cols, dt1Cols + dt2Cols - 1);
            sheet.AddMergedRegion(region3);
            //((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region3, CellBorderType.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
            rowIndex++;

            InsertData(workbook, sheet, rowIndex, dt1, dt2, memberHits, nonMemberHits, totalHits);
            InsertRow(dt3, workbook, "信息点击数据");
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                sheet.Dispose();
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                return ms;
            }
        }

        public static void InsertData(HSSFWorkbook workbook, ISheet sheet, Int32 rowIndex,
            DataTable dt1, DataTable dt2, Int32 memberHits, Int32 nonMemberHits, Int32 totalHits)
        {
            Int32 maxCount = 0;
            Int32 dt1Rows = dt1.Rows.Count;
            Int32 dt2Rows = dt2.Rows.Count;
            Int32 dt1Cols = dt1.Columns.Count;
            Int32 dt2Cols = dt2.Columns.Count;
            maxCount = dt1Rows > dt2Rows ? dt1Rows : dt2Rows;
            maxCount += 2;

            //取得列宽
            Int32[] arrColWidth = new Int32[dt1Cols + dt2Cols];

            for (Int32 i = 0; i < dt1.Rows.Count; i++)
            {
                for (Int32 j = 0; j < dt1.Columns.Count; j++)
                {
                    Int32 intTemp = Encoding.GetEncoding(936).GetBytes(dt1.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            for (Int32 i = 0; i < dt2.Rows.Count; i++)
            {
                for (Int32 j = dt1Cols; j < dt2.Columns.Count + dt1Cols; j++)
                {
                    Int32 intTemp = Encoding.GetEncoding(936).GetBytes(dt2.Rows[i][j - dt1Cols].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            for (Int32 ridx = rowIndex; ridx < maxCount + rowIndex; ridx++)
            {
                IRow newRow = sheet.CreateRow(ridx);
                for (Int32 cidx = 0; cidx < dt1Cols + dt2Cols; cidx++)
                {
                    //设置列宽
                    sheet.SetColumnWidth(cidx, (arrColWidth[cidx] + 10) * 256);
                    string drValue = String.Empty;
                    if (cidx < dt1Cols)
                    {
                        if (ridx - rowIndex >= dt1Rows)
                            drValue = "";
                        else
                            drValue = dt1.Rows[ridx - rowIndex][cidx].ToString().Trim();
                    }
                    else
                    {
                        if (ridx - rowIndex >= dt2Rows)
                            drValue = "";
                        else
                            drValue = dt2.Rows[ridx - rowIndex][cidx - dt1Cols].ToString().Trim();
                    }
                    ICell newCell = newRow.CreateCell(cidx);
                    newCell.SetCellValue(drValue);
                    ICellStyle cellStyle = workbook.CreateCellStyle();
                    cellStyle.BorderBottom = CellBorderType.THIN;
                    cellStyle.BorderLeft = CellBorderType.THIN;
                    cellStyle.BorderRight = CellBorderType.THIN;
                    cellStyle.BorderTop = CellBorderType.THIN;

                    cellStyle.TopBorderColor = HSSFColor.BLACK.index;
                    cellStyle.BottomBorderColor = HSSFColor.BLACK.index;
                    cellStyle.LeftBorderColor = HSSFColor.BLACK.index;
                    cellStyle.RightBorderColor = HSSFColor.BLACK.index;
                    IFont font = workbook.CreateFont();
                    font.FontName = "宋体";
                    font.FontHeightInPoints = 12;
                    cellStyle.SetFont(font);
                    newCell.CellStyle = cellStyle;
                }
            }
            IRow totalRow1 = sheet.GetRow(dt1Rows + rowIndex + 1);
            ICell totalCell1 = totalRow1.GetCell(0);
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;
            style.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            style.FillPattern = FillPatternType.LEAST_DOTS;
            style.FillBackgroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
            IFont font2 = workbook.CreateFont();
            font2.FontName = "宋体";
            font2.FontHeightInPoints = 12;
            style.SetFont(font2);
            totalCell1.SetCellValue(String.Format(">>合计总数：{0}个", memberHits));
            totalCell1.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(dt1Rows + rowIndex + 1, dt1Rows + rowIndex + 1, 0, 2));

            IRow totalRow2 = sheet.GetRow(dt2Rows + rowIndex + 1);
            ICell totalCell2 = totalRow2.GetCell(dt1Cols);
            totalCell2.SetCellValue(String.Format(">>合计总数：{0}个", memberHits));
            totalCell2.CellStyle = style;
            sheet.AddMergedRegion(new CellRangeAddress(dt2Rows + rowIndex + 1, dt2Rows + rowIndex + 1, dt1Cols, dt1Cols + dt2Cols - 1));
            if (dt1Rows > dt2Rows)
            {
                sheet.AddMergedRegion(new CellRangeAddress(dt2Rows + rowIndex + 2, maxCount + rowIndex - 1, dt1Cols, dt1Cols + dt2Cols - 1));
            }
            else if (dt1Rows < dt2Rows)
            {
                sheet.AddMergedRegion(new CellRangeAddress(dt1Rows + rowIndex + 2, maxCount + rowIndex - 1, 0, dt1Cols - 1));
            }

            rowIndex++;

            ICellStyle style2 = workbook.CreateCellStyle();
            style2.BorderBottom = CellBorderType.THIN;
            style2.BorderLeft = CellBorderType.THIN;
            style2.BorderRight = CellBorderType.THIN;
            style2.BorderTop = CellBorderType.THIN;
            style2.TopBorderColor = HSSFColor.BLACK.index;
            style2.BottomBorderColor = HSSFColor.BLACK.index;
            style2.LeftBorderColor = HSSFColor.BLACK.index;
            style2.RightBorderColor = HSSFColor.BLACK.index;
            IFont font3 = workbook.CreateFont();
            font3.FontName = "宋体";
            font3.FontHeightInPoints = 12;
            font3.Color = HSSFColor.SKY_BLUE.index;
            style2.SetFont(font3);

            IRow row1 = sheet.CreateRow(maxCount + rowIndex);
            ICell cell11 = row1.CreateCell(0);
            cell11.SetCellValue("[信息]会员访问量");
            cell11.CellStyle = style2;

            rowIndex++;
            IRow row2 = sheet.CreateRow(maxCount + rowIndex);
            ICell cell21 = row2.CreateCell(0);
            cell21.SetCellValue("[信息]非会员访问量");
            cell21.CellStyle = style2;
            rowIndex++;
            IRow row3 = sheet.CreateRow(maxCount + rowIndex);
            ICell cell31 = row3.CreateCell(0);
            cell31.SetCellValue("[信息]访问总量");
            cell31.CellStyle = style2;

            ICellStyle style3 = workbook.CreateCellStyle();
            style3.BorderBottom = CellBorderType.THIN;
            style3.BorderLeft = CellBorderType.THIN;
            style3.BorderRight = CellBorderType.THIN;
            style3.BorderTop = CellBorderType.THIN;
            style3.TopBorderColor = HSSFColor.BLACK.index;
            style3.BottomBorderColor = HSSFColor.BLACK.index;
            style3.LeftBorderColor = HSSFColor.BLACK.index;
            style3.RightBorderColor = HSSFColor.BLACK.index;
            IFont font4 = workbook.CreateFont();
            font4.FontName = "宋体";
            font4.FontHeightInPoints = 12;
            style3.SetFont(font4);

            ICell cell12 = row1.CreateCell(1);
            cell12.CellStyle = style3;
            cell12.SetCellValue(memberHits);
            ICell cell22 = row2.CreateCell(1);
            cell22.CellStyle = style3;
            cell22.SetCellValue(nonMemberHits);
            ICell cell32 = row3.CreateCell(1);
            cell32.CellStyle = style3;
            cell32.SetCellValue(totalHits);

        }

        /// <summary>
        /// 创建字体
        /// </summary>
        /// <param name="workbook">工作薄</param>
        /// <param name="fontName">字体名称</param>
        /// <param name="fontSize">尺寸</param>
        /// <param name="bold">粗体</param>
        /// <param name="color">字体颜色</param>
        /// <returns></returns>
        public static IFont CreateFont(HSSFWorkbook workbook, string fontName, short fontSize, short bold, short color)
        {
            IFont font = workbook.CreateFont();
            font.FontName = fontName;
            font.FontHeightInPoints = fontSize;
            font.Boldweight = bold;
            font.Color = color;
            return font;
        }

        /// <summary>
        /// 创建单元格样式
        /// </summary>
        /// <param name="workbook">工作薄</param>
        /// <param name="horizontal">水平位置</param>
        /// <param name="vertical">垂直位置</param>
        /// <param name="border">边框线条</param>
        /// <param name="borderColor">线条颜色</param>
        /// <returns></returns>
        public static ICellStyle CreateCellStyle(HSSFWorkbook workbook, HorizontalAlignment horizontal, VerticalAlignment vertical, CellBorderType border, short borderColor)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = horizontal;
            style.VerticalAlignment = vertical;
            style.BorderBottom = border;
            style.BorderLeft = border;
            style.BorderRight = border;
            style.BorderTop = border;
            style.TopBorderColor = borderColor;
            style.BottomBorderColor = borderColor;
            style.LeftBorderColor = borderColor;
            style.RightBorderColor = borderColor;
            return style;
        }
    }

    //排序实现接口 不进行排序 根据添加顺序导出   
    public class NoSort : System.Collections.IComparer
    { public int Compare(object x, object y) { return -1; } }
}
/*调用方法
 //导出数据列 实现根据添加顺序导出列
        StarTech.NPOI.NPOIHelper.ListColumnsName = new SortedList(new StarTech.NPOI.NoSort());
        StarTech.NPOI.NPOIHelper.ListColumnsName.Add("MemberName", "姓名");
        StarTech.NPOI.NPOIHelper.ListColumnsName.Add("username", "账号");
        StarTech.NPOI.NPOIHelper.ListColumnsName.Add("starttime", "登陆时间");
        StarTech.NPOI.NPOIHelper.ListColumnsName.Add("lasttime", "在线到期时间");
        StarTech.NPOI.NPOIHelper.ListColumnsName.Add("state", "状态");
        Response.Clear();
        Response.BufferOutput = false;
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        string filename = HttpUtility.UrlEncode(DateTime.Now.ToString("在线用户yyyyMMdd"));
        Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
        Response.ContentType = "application/ms-excel";
        StarTech.NPOI.NPOIHelper.ExportExcel(dtSource, Response.OutputStream);
        Response.Close();*/